package com.scuec.trainingmanagementsys.mapper;
import com.scuec.trainingmanagementsys.entity.Team;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface TeamMapper {
    // 添加团队（允许leaderID为空）
    @Insert("INSERT INTO team (teamName, projectID, leaderID) VALUES (#{teamName}, #{projectID}, #{leaderID})")
    int addTeam(Team team);

    // 删除团队
    @Delete("DELETE FROM team WHERE teamID = #{teamID}")
    int deleteTeam(Integer teamID);

    // 更新团队
    @Update("UPDATE team SET teamName = #{teamName}, projectID = #{projectID}, leaderID = #{leaderID} WHERE teamID = #{teamID}")
    int updateTeam(Team team);

    // 根据 ID 查询单个团队（不关联）
    @Select("SELECT * FROM team WHERE teamID = #{teamID}")
    Team getTeam(Integer teamID);

    // 查询所有团队（不关联）
    @Select("SELECT * FROM team")
    List<Team> getAllTeams();

    // 关联查询团队、项目、负责人信息（明确字段别名）
    @Select("SELECT " +
            "t.teamID, " +
            "t.teamName, " +
            "t.projectID, " +
            "p.projectName, " +          // 项目名称（来自 project 表）
            "s.studentName AS leaderName " +  // 负责人姓名（来自 student 表，根据 teammember 表的组长信息）
            "FROM team t " +
            "LEFT JOIN project p ON t.projectID = p.projectID " +
            "LEFT JOIN teammember tm ON t.teamID = tm.TeamID AND tm.MemberRole = 'Leader' " +
            "LEFT JOIN student s ON tm.StudentID = s.studentID")
    List<Team> listWithRelations();

    // 修改后的成员查询（关联 teammember 和 student 表）
    @Select("SELECT " +
            "tm.TeamID, " +               // 注意字段名大小写
            "tm.StudentID, " +            // 注意字段名大小写
            "s.studentName AS memberName, " +
            "tm.MemberRole " +            // 注意字段名大小写
            "FROM teammember tm " +      // 表名改为 teammember
            "LEFT JOIN student s ON tm.StudentID = s.studentID " +
            "WHERE tm.TeamID = #{teamID}")
    List<Map<String, Object>> getTeamMembers(@Param("teamID") Integer teamID);

    @Select("SELECT * FROM team WHERE projectID = #{projectId}")
    List<Team> getTeamsByProjectId(Integer projectId);
}